# -*- coding: utf-8 -*-


'''比较两种方式的select效率

   首先需要执行insert.py,插入大约200000条记录
   然后比较django objects.filter()和sql同时选出2000条记录的效率
   mixed: 使用django的connection来完成纯粹的sql工作,函数与sselect基本相同,
          但传入的connection不同,同时参数的传递风格(paramstyle)不同,django用%s, sqlite用?方式
   mixed2: 使用sql connection,但返回的数据转化为django的对象

    2000条:
        django: 0.141000032425      0.125
        sql:    0.0149998664856     0.0309998989105
    10000条:
        django: 0.483999967575  0.483999967575  0.5
        sql:    0.18799996376   0.171999931335  0.172

    100000条:
        django: 4.76599979401   4.75
        sql:    2.93700003624   2.93700003624
        mixed:  3.03099989891
        这个计算和内存耗用有关,如果屏蔽掉django方式,则:
            sql:    1.70300006866      
            mixed:  1.547

    对于检索数量较多时,性能差异在1:2左右,少量时估计差异在3-8倍

    对于少量数据(2000条左右), 多次访问(2000次左右)的比较
    2000条,1000次:
        django:     91.125
        sql:        23.391  23.5780000687
        mixed:      25.625
    2000条,2000次:
        django:     182.95299983
        sql:        47.64           46.7349998951
        mixed:      48.8900001049
    典型情况  750条,2000次:
        django:     69.9689998627
        sql:        17.187  16.969  16.7030000687   16.796
        mixed:      17.1400001049   17.672
        mixed2:     98.734          99.4219999313
        典型情况下,mixed2的效能最差,这个可能和创建新对象时django的内务操作有关. 
                   mixed的性能可以接受. 而且受到django transaction的保护


'''
from django.core.management import setup_environ
import wolfox.foxit.other_settings.settings_benchmark as target
setup_environ(target)
from django.db import transaction

from wolfox.foxit.benchmark.models import Normal

TIMES = 200000
STEP = 10000
id_from = 11000
id_to = 111000

def prepare(connection):
    cursor = connection.cursor()
    for i in xrange(TIMES):
        cursor.execute('insert into benchmark_normal(name,i1,i2,i3,i4,i5,i6) values(?,?,?,?,?,?,?)',(u'test',i,i,i,i,i,i))
        if(i%STEP == 0):
            print 'save %d' % i
    connection.commit()
    

def dselect():
    q1 = Normal.objects.filter(id__gte = id_from).filter(id__lte = id_to)
    lq = list(q1)
    return lq


class dummy(object):
    def __init__(self,id,name,i1,i2,i3,i4,i5,i6):
        self.id, self.name,self.i1,self.i2,self.i3,self.i4,self.i5,self.i6 = id,name,i1,i2,i3,i4,i5,i6


def sselect(connection):
    cursor = connection.cursor()
    cursor.execute('select id,name,i1,i2,i3,i4,i5,i6 from benchmark_normal where id>=? and id<=?',(id_from,id_to))
    rows = cursor.fetchall()
    records = [dummy(id,name,i1,i2,i3,i4,i5,i6) for id,name,i1,i2,i3,i4,i5,i6 in rows]
    return records

def mixed(connection):
    cursor = connection.cursor()
    cursor.execute('select id,name,i1,i2,i3,i4,i5,i6 from benchmark_normal where id>=%s and id<=%s',(id_from,id_to))
    rows = cursor.fetchall()
    records = [dummy(id,name,i1,i2,i3,i4,i5,i6) for id,name,i1,i2,i3,i4,i5,i6 in rows]
    return records

def mixed2(connection):
    cursor = connection.cursor()
    cursor.execute('select id,name,i1,i2,i3,i4,i5,i6 from benchmark_normal where id>=? and id<=?',(id_from,id_to))
    rows = cursor.fetchall()
    records = [Normal(id=id,name=name,i1=i1,i2=i2,i3=i3,i4=i4,i5=i5,i6=i6) for id,name,i1,i2,i3,i4,i5,i6 in rows]
    return records


if __name__ == '__main__':
    from time import time

    import sqlite3
    import os.path
    par_path = os.path.join(os.path.dirname(__file__),os.pardir)
    #print par_path
    #准备

    conn_sql = sqlite3.connect(os.path.join(par_path,'benchmark\\benchmark.db'))

    prepare(conn_sql)
    conn_sql.commit()
    conn_sql.close()

    ttimes = 1
    #django方式
    f = time()
    for x in xrange(ttimes):
        dselect()
        if(x%50 == 0):
            print 'now : %d' % x
    t = time()
    print t-f

    #mixed方式 django_conn + sql
    from django.db import connection as conn_django
    #mixed方式
    f = time()
    for x in xrange(ttimes):
        mixed(conn_django)
        if(x%50 == 0):
            print 'mixed now : %d' % x
    t = time()
    print t-f


    conn_sql = sqlite3.connect(os.path.join(par_path,'benchmark\\benchmark.db'))
    #mixed2方式
    f = time()
    for x in xrange(ttimes):
        mixed2(conn_sql)
        if(x%50 == 0):
            print 'mixed2 now : %d' % x
    t = time()
    print t-f

    #sql方式
    f = time()
    for x in xrange(ttimes):
        sselect(conn_sql)
    t = time()
    print t-f    
    conn_sql.close()



